package org.lanqiao.tjut.dao;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lanqiao.tjut.bean.UsersBeanStu;
import org.lanqiao.tjut.bean.UsersBeanTea;
import org.lanqiao.tjut.db.DBDriverFactory;

public class UserInfoManagerStuDao {

	/**
	 * 获取所有的用户成绩
	 * 
	 * @return 用户信息集
	 */
	public List<UsersBeanStu> getAllUsersInfo(String strUnb) {
		// 查询数据库的sql语句
		String strSql = "SELECT c.*,s.* FROM EASCOU c,EASSCO s WHERE c.COUID=s.COUID AND USERNUM=?";
		List<UsersBeanStu> lstUsersStu = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<UsersBeanStu>(UsersBeanStu.class), new Object[] { strUnb });
		return lstUsersStu;
	}

	/**
	 * 获取学生已选课程
	 * 
	 * @return 课程信息集
	 */
	public List<UsersBeanStu> getAllStuCou(String strUnb) {
		// 查询数据库的sql语句
		String strSql = "SELECT c.*,t.TEANAME FROM EASCOU c,EASSCO s,EASTEA t WHERE c.COUID=s.COUID AND t.TEANUM=c.TEANUM AND USERNUM=?";
		List<UsersBeanStu> lstUsersStu = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<UsersBeanStu>(UsersBeanStu.class), new Object[] { strUnb });
		return lstUsersStu;
	}

	/**
	 * 获取学生未选课程
	 * 
	 * @return 课程信息集
	 */
	public List<UsersBeanStu> getAllStuCouNo(String strUnb) {
		// 查询数据库的sql语句
		String strSql = "SELECT c.*,t.TEANAME FROM EASCOU c,EASTEA t WHERE t.TEANUM=c.TEANUM MINUS SELECT c.*,t.TEANAME FROM EASCOU c,EASSCO s,EASTEA t WHERE c.COUID=s.COUID AND t.TEANUM=c.TEANUM  AND USERNUM=?";
		List<UsersBeanStu> lstUsersStu = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanListHandler<UsersBeanStu>(UsersBeanStu.class), new Object[] { strUnb });
		return lstUsersStu;
	}

	/**
	 * 删除指定id的课程
	 * 
	 * @param userId
	 *            用户id
	 * @return 删除结果集数目
	 */
	public int deleteStuCou(UsersBeanTea usersBT) {
		int re_i = 0;
		// 执行的sql语句
		String strSql = "DELETE FROM EASSCO WHERE USERNUM=? AND COUID=? ";
		Object[] params = new Object[] {usersBT.getUsernum(),usersBT.getCouid()};
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, params);
		return re_i;
	}

	/**
	 * 添加学生课程
	 * 
	 * @param usersB
	 *            用户信息对象
	 * @return 保存结果的记录行数
	 */

	public int addStuCou(UsersBeanTea usersBT) {
		int re_i = 0;
		// 新增处理
		String strSql = "INSERT INTO EASSCO(USERNUM, COUID ,COUSCO) VALUES(?,?,?)";
		Object[] params = new Object[] { usersBT.getUsernum(), usersBT.getCouid(), usersBT.getCousco()};
		
		// 保存数据                                                                           
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, params);
		return re_i;
	}



}
